import xlrd
import pandas as pd
import os
import shutil
import re
from glob import glob
from tqdm import tqdm
from pony import orm

from postgres.db_build import MedicineNormalize, ServiceNormalize, ConsumableNormalize, Hospital_coding, \
    Hospital_mapping, HospitalWebData, Country, Hospital, ChineseMedicineNormalize, ServiceVersion
from utils.post_process import post_process, medicine_normalize, service_normalize
from utils.DataProcess import DataProcess
from utils.yibao2medicine import yibao2medicine
from utils.module.medicine_normalization import MedicineNormalizaiton
from utils.module.medical_service_normalization import MedicalServiceNormalization
from utils.module.medical_consumables_normalization import ConsumablesNormalization


class ProvinceVersionDataProcess(object):
    """
    版本库清洗入库@nieyan
    输入:查药扣费各城市整理文件夹->文件
    输出:
    - 版本库中不存在的别称
    - 查药扣费整理文件内词条归一化去重
    步骤:
    - 读取查药扣费一个城市整理excel文件,不同的sheet页分别读取,分别处理
    - 归一化处理
        - 预处理统一文本格式
    """

    def __init__(self, dir_path):
        self.dir_path = dir_path
        self.province = dir_path.split("-")[-1]

        # 后处理
        self.data_process = DataProcess()
        # 归一化模块初始化
        self.medicine_normalization = MedicineNormalizaiton()
        self.service_normalization = MedicalServiceNormalization()
        self.consumables_normalization = ConsumablesNormalization()
        # 表头映射
        self.columns_dict = {
            "项目编码*": "version_code",
            "项目名称*": "version_name"
        }
        # 版本库表头
        self.columns = ["version_code", "version_name", "data_source",
                        "type", "standard_code", "standard_name", "remark", "classification"]
        # 添加后处理的pass_flag
        # self.columns.insert(self.columns.index("customer_name") + 1, "pass_flag")
        # 后处理自动通过标志
        self.post_pass_flag = 2
        # 添加归一化的pass_flag
        self.columns.insert(self.columns.index("standard_name") + 1, "pass_flag2")
        # 归一化自动通过标志
        self.normalize_pass_flag = 1
        # 后处理人工审核表头
        self.post_process_manual_columns = ["full_name", "customer_name", "pass_flag"]
        # 归一化人工审核表头
        self.normalize_manual_columns = ["version_code", "version_name", "data_source","type", "standard_code",
                                         "standard_name", "remark", "classification"]
        # 大项人工审核表头
        self.type_manual_columns = ["full_name", "customer_name", "type"]

        # 输入文件路径
        file_name = "医保三目录数据"
        files = os.listdir(self.dir_path)
        for file in files:
            if file_name in file and "归一化" not in file and "~" not in file:
                file_name = file
                break
        self.input_file_path = os.path.join(self.dir_path, file_name)
        # 查药扣费归一化后保存文件
        self.output_processed_file = os.path.join(self.dir_path, "归一化后_" + file_name)
        # 后处理结果
        self.post_process_file_path = "1、后处理.xlsx"
        # 人工处理文件
        self.post_process_manual_file_path = "1、后处理人工审核.xlsx"
        # 人工审核后回填文件
        self.post_process_update_file_path = "2、更新后处理数据.xlsx"
        # 归一化文件
        self.normalize_file_path = os.path.join(self.dir_path, "1、归一化结果.xlsx")
        # 归一化审核文件
        self.normalize_manual_file_path = os.path.join(self.dir_path, "2、归一化审核文件.xlsx")
        # 归一化审核后回填文件
        self.normalize_update_file_path = os.path.join(self.dir_path, "3、更新归一化结果.xlsx")
        # 添加大项文件
        self.add_type_file_path = "3、添加大项.xlsx"
        self.add_type_manual_file_path = "3、人工添加大项.xlsx"
        self.add_type_update_file_path = "4、更新大项.xlsx"
        self.output_file_path = "可入库inphile_big_data_version.xlsx"
        # 冗余项
        self.add_parts_file_path = "5、冗余项.xlsx"

        self.menu = str("请输入需要运行的程序：\n"
                        "1、归一化\n"
                        "2、归一化人工审核结果回填\n"
                        "3、生成入库文件\n"
                        "4、处理异常的药品或服务名称\n"
                        "输入其他跳过当前流程\n"
                        "回车键确认\n")

    # 主入口
    def main(self):
        if not os.path.exists(self.input_file_path):
            print("输入文件不存在或命名错误")
            return 0
        key = input(self.menu)
        if "1" in key:
            self.main_process()
        if "2" in key:
            self.update_manual_data_normal()
        if "3" in key:
            self.output()
        if "4" in key:
            path = input("请粘贴需要处理的excel路径:\n")
            self.cell_process(path)

    # 归一化处理主流程
    def main_process(self):
        path = self.input_file_path
        xl_file = pd.ExcelFile(path)
        sheet_names = xl_file.sheet_names
        # 保存处理后的三目录数据
        writer = pd.ExcelWriter(self.output_processed_file, engine='openpyxl')
        df_output = pd.DataFrame()
        df_check = pd.DataFrame()
        num = 1
        for sheet_name in sheet_names:
            if "药品" in sheet_name:
                df, output, check = self.medicine_normal_process(xl_file, sheet_name)
                df.to_excel(index=False, excel_writer=writer, sheet_name=sheet_name, encoding="GBK")
                df_output = df_output.append(output)
                df_check = df_check.append(check)
            elif "服务" in sheet_name:
                df, output, check = self.service_normal_process(xl_file, sheet_name)
                df.to_excel(index=False, excel_writer=writer, sheet_name=sheet_name, encoding="GBK")
                df_output = df_output.append(output)
                df_check = df_check.append(check)
            elif "耗材" in sheet_name:
                df = pd.read_excel(xl_file, sheet_name=sheet_name, dtype=str, keep_default_na=False)
                # 统一数据格式
                df = df.applymap(lambda y: self.ch_en(y))
                df = df.drop_duplicates()
                df.to_excel(index=False, excel_writer=writer, sheet_name=sheet_name, encoding="GBK")
            print(sheet_name + "  保存成功%d/%d" % (num, len(sheet_names)))
            num += 1
        writer.save()
        writer.close()
        df_output.to_excel(self.normalize_file_path, index=False)
        df_check.to_excel(self.normalize_manual_file_path, index=False)

    # 药品归一化
    @orm.db_session()
    def medicine_normal_process(self, xl_file, sheet_name):
        df = pd.read_excel(xl_file, sheet_name=sheet_name, dtype=str, keep_default_na=False)
        # 统一数据格式
        df = df.applymap(lambda y: self.ch_en(y))
        df = df.drop_duplicates()
        # 转化表头
        new_df = pd.DataFrame()
        for old_name, new_name in self.columns_dict.items():
            new_df[new_name] = df[old_name]
        table = []
        for index in tqdm(range(0, len(df[:]))):
            # 版本库格式
            item = new_df.iloc[index, :]
            series = pd.Series(item, index=self.columns)
            series = series.fillna("")

            full_name = item["version_name"]
            customer_name = item["version_name"]
            series["data_source"] = self.province
            series["classification"] = "药品"

            if customer_name:
                # 名称映射
                qs = MedicineNormalize.select(standard_name=full_name)
                if qs.count() > 0:
                    for it in qs:
                        series["type"] = "名称映射"
                        nhsa_code = it.nhsa_code
                        series["standard_code"] = nhsa_code
                        standard_name = it.standard_name
                        series["standard_name"] = standard_name
                        series["pass_flag2"] = 1
                        break

                # 码值映射
                if not series["pass_flag2"] and series["version_code"]:
                    series["type"] = "码值映射"
                    version_code = series["version_code"]
                    qs = MedicineNormalize.select(nhsa_code=version_code)
                    if qs.count() > 0:
                        for it in qs:
                            nhsa_code = it.nhsa_code
                            series["standard_code"] = nhsa_code
                            standard_name = it.standard_name

                            if standard_name != full_name and (
                                    standard_name in full_name or full_name in standard_name):
                                series["standard_name"] = ""
                            else:
                                series["standard_name"] = standard_name
                                series["pass_flag2"] = 1
                            break
                if not series["pass_flag2"]:
                    series["type"] = "算法自动通过"
                    standard_name, _ = self.medicine_normalization.medicine_accurate_match(full_name, customer_name)
                    if _:
                        standard_name = standard_name[0]
                        series["standard_name"] = standard_name
                        series["pass_flag2"] = 1
                        qs = MedicineNormalize.select(standard_name=standard_name)
                        if len(qs) > 0:
                            for it in qs:
                                nhsa_code = it.nhsa_code
                                series["standard_code"] = nhsa_code
                                break
                    else:
                        standard_name, _ = self.medicine_normalization.normalize(customer_name)
                        pass_flag2 = 0
                        if type(standard_name[0]) == dict:
                            standard_name = standard_name[0]["name"]
                        else:
                            standard_name = standard_name[0]
                        # 当返回候选值时，如果第一推荐值包含于原值，则认为数据可信
                        standard_name = self.ch_en(standard_name)
                        if standard_name in full_name:
                            pass_flag2 = 1
                            qs = MedicineNormalize.select(standard_name=standard_name)
                            if len(qs) > 0:
                                for it in qs:
                                    nhsa_code = it.nhsa_code
                                    series["standard_code"] = nhsa_code
                                    break
                        # 当得到可信数据或者返回值自动通过时，数据入库
                        if type(standard_name) == str and standard_name:
                            series["standard_name"] = standard_name
                            series["pass_flag2"] = pass_flag2
            if series["version_code"] == series["standard_code"]:
                series["remark"] = "版本特有词条"
            else:
                series["remark"] = ""
            table.append(series)
        df_output = pd.DataFrame(table)
        df_output = df_output.fillna("")

        df_check = df_output[df_output["pass_flag2"] != self.normalize_pass_flag][self.normalize_manual_columns]
        df_check.drop_duplicates(inplace=True)

        return df, df_output, df_check

    # 服务归一化
    @orm.db_session()
    def service_normal_process(self, xl_file, sheet_name):
        df = pd.read_excel(xl_file, sheet_name=sheet_name, dtype=str, keep_default_na=False)
        # 统一数据格式
        df = df.applymap(lambda y: self.ch_en(y))
        df = df.drop_duplicates()
        # 转化表头
        new_df = pd.DataFrame()
        for old_name, new_name in self.columns_dict.items():
            new_df[new_name] = df[old_name]
        table = []
        for index in tqdm(range(0, len(df[:]))):
            # 版本库格式
            item = new_df.iloc[index, :]
            series = pd.Series(item, index=self.columns)
            series = series.fillna("")

            full_name = item["version_name"]
            customer_name = item["version_name"]
            series["data_source"] = self.province
            series["classification"] = "医疗服务"

            if customer_name:
                # 名称映射
                qs = orm.select(
                    service
                    for service in ServiceVersion
                    if service.version_name == full_name
                )
                if len(qs) > 0:
                    for it in qs:
                        series["type"] = "名称映射"
                        standard_code = it.standard_code
                        series["standard_code"] = standard_code
                        standard_name = it.standard_name
                        series["standard_name"] = standard_name
                        if self.province[:2] in it.data_source or "国家" in it.data_source:
                            series["pass_flag2"] = 1
                            break
                        else:
                            series["pass_flag2"] = 0

                # 码值映射
                if not series["pass_flag2"] and series["version_code"]:
                    series["type"] = "码值映射"
                    version_code = series["version_code"]
                    qs = orm.select(
                        service
                        for service in ServiceVersion
                        if service.version_code == version_code
                    )
                    if len(qs) > 0:
                        for it in qs:
                            standard_code = it.standard_code
                            series["standard_code"] = standard_code
                            standard_name = it.standard_name
                            series["standard_name"] = standard_name
                            series["pass_flag2"] = 1
                            break
                    else:
                        qs = orm.select(
                            service
                            for service in ServiceVersion
                            if version_code in service.version_code
                        )
                        if len(qs) > 0:
                            for it in qs:
                                standard_code = it.standard_code
                                series["standard_code"] = standard_code
                                standard_name = it.standard_name
                                series["standard_name"] = standard_name
                                series["pass_flag2"] = 0
                                break
                        else:
                            qs = orm.select(
                                service
                                for service in ServiceVersion
                                if version_code[2:] in service.version_code
                            )
                            if len(qs) > 0:
                                for it in qs:
                                    standard_code = it.standard_code
                                    series["standard_code"] = standard_code
                                    standard_name = it.standard_name
                                    series["standard_name"] = standard_name
                                    series["pass_flag2"] = 0
                                    break
                if not series["pass_flag2"]:
                    series["type"] = "算法自动通过"
                    standard_name, _ = self.service_normalization.medical_service_accurate_match(full_name,
                                                                                                 customer_name)
                    if _:
                        standard_name = standard_name[0]
                        series["standard_name"] = standard_name
                        series["pass_flag2"] = 1
                        qs = ServiceNormalize.select(standard_name=standard_name)
                        if len(qs) > 0:
                            for it in qs:
                                nhsa_code = it.nhsa_code
                                series["standard_code"] = nhsa_code
                                break
                    else:
                        standard_name = self.service_normalization.normalize(customer_name)
                        pass_flag2 = 0
                        if type(standard_name[0]) == list:
                            standard_name = standard_name[0]
                        if type(standard_name[0]) == dict:
                            standard_name = standard_name[0]['candidate_medical_service']
                        else:
                            standard_name = standard_name[0]
                        standard_name = self.ch_en(standard_name)
                        # 当返回候选值时，如果第一推荐值包含于原值，则认为数据可信
                        if standard_name in full_name:
                            pass_flag2 = 1
                            qs = ServiceNormalize.select(standard_name=standard_name)
                            if len(qs) > 0:
                                for it in qs:
                                    nhsa_code = it.nhsa_code
                                    series["standard_code"] = nhsa_code
                                    break
                        # 当得到可信数据或者返回值自动通过时，数据入库
                        if type(standard_name) == str and standard_name:
                            series["standard_name"] = standard_name
                            series["pass_flag2"] = pass_flag2
            if series["version_name"] == series["standard_name"]:
                series["remark"] = ""
            else:
                series["remark"] = "版本特有词条"
            table.append(series)
        df_output = pd.DataFrame(table)
        df_output = df_output.fillna("")

        df_check = df_output[df_output["pass_flag2"] != self.normalize_pass_flag][self.normalize_manual_columns]
        df_check.drop_duplicates(inplace=True)

        return df, df_output, df_check

    # 统一为英文符号
    def ch_en(self, word):
        # 统一为英文符号
        intab = u"！—。【】“”‘’《》—「」『』〖〗〔〕〓︰﹒﹕﹙﹚﹝﹞﹢〈〉∽∶‥–‒Ι×[]（）＃［］{}：％＊﹛﹜，－／÷＿﹡；ⅹ\\"
        outab = u"!-.()\"\"\'\'()-()()()()=:.:()()+()~::--i*()()#()():%*(),-//_*;*/"
        trantab = str.maketrans(intab, outab)  # 一一映射。
        word = word.translate(trantab)  # 返回字符串S的副本，其中每个字符都已通过给定的转换表进行映射。
        # 统一罗马数字
        word = word.replace("Ⅰ", "I")
        word = word.replace("Ⅱ", "II")
        word = word.replace("Ⅲ", "III")
        word = word.replace("Ⅳ", "IV")
        word = word.replace("Ⅴ", "V")
        word = word.replace("Ⅵ", "VI")
        word = word.replace("Ⅶ", "VII")
        word = word.replace("Ⅷ", "VIII")
        word = word.replace("Ⅸ", "IX")
        word = word.replace("Ⅹ", "X")

        if re.findall("＜.*?＞", word):
            word = word.replace("＜", "(")
            word = word.replace("＞", ")")
        if re.findall("<.*?>", word):
            word = word.replace("<", "(")
            word = word.replace(">", ")")
        # 空格处理
        word = re.sub(
            "(?<=[^0-9a-zA-Z])[ ]+(?=[0-9a-zA-Z])|(?<=[0-9a-zA-Z])[ ]+(?=[^0-9a-zA-Z])|"
            "(?<=[^0-9a-zA-Z])[ ]+(?=[^0-9a-zA-Z])|(?<=[()])[ ]+|[ ]+(?=[()\-])",
            "", word)
        # word = re.sub(" ", "", word)
        return word

    # 通过医院名称搜索医院编码
    @orm.db_session
    def get_hospital_coding(self, hospital):
        hospital_id = ''
        qs = orm.select(
            item
            for item in Hospital_coding
            if
            item.hospital_name_nhsa == hospital
        )
        for item in qs:
            hospital_id = item.hospital_id
            break
        if hospital_id == '':
            qs = orm.select(
                item
                for item in Hospital_mapping
                if
                item.hospital_name_bill == hospital
            )
            for item in qs:
                hospital_id = item.hospital_id
                break
        return hospital_id

    # 输入文件校验
    def input_check(self, columns, path):
        """
        输入校验：
        输入文件表头必须是医院网站库表头的子集，表头命名必须一致
        """
        if set(columns) <= set(self.columns):
            pass
        else:
            raise Exception(f"{os.path.basename(path)}文件表头有误")

    # 后处理审核完成后更新输入文件校验
    def manual_input_check(self, columns, path):
        """
        输入校验：
        输入文件表头必须包含后处理人工审核的所有表头，表头命名必须一致
        """
        if set(columns) >= set(self.post_process_manual_columns):
            pass
        else:
            raise Exception(f"{os.path.basename(path)}文件表头有误")

    # 后处理自动通过
    @orm.db_session()
    def post_auto_pass(self, remark, customer_name, full_name):
        # TODO:存在漏洞，无法确定词条在所有表中模糊匹配可以得到的条数，如果一个词条在一个库中精准匹配，在另一个库存在包含关系，则会误打标签为2
        # TODO:根本上解决需要构建新的数据库，将所有词条包含进去
        # 先匹配原值统一中英文符号后的结果，能匹配上，直接用原值作为甲方回传值
        # 过甲方回传库和归一化标准词条，精准匹配：
        # 能匹配上，再进行包含匹配：
        # 能匹配上且只匹配上一个，后处理值自动通过，标签为2；
        # 匹配到多个，但是原值和甲方值相同，自动通过，标签为2；
        # 匹配到多个，但是原值和甲方值不同，需要人看一下，标签为1；

        # full_name = self.ch_en(full_name)
        if "特需" in full_name:
            return 2, customer_name
        pass_flag = 0
        if remark == "药品":
            qs1 = orm.count(
                medicine
                for medicine in MedicineNormalize
                if full_name in medicine.standard_name
            )
            if qs1 > 0:
                pass_flag = 2
                customer_name = full_name
            else:
                qs1 = MedicineNormalize.select(standard_name=customer_name)
                if qs1.count() > 0:
                    qs1 = orm.count(
                        item.standard_name
                        for item in MedicineNormalize
                        if customer_name in item.standard_name)
                    if qs1 == 1:
                        pass_flag = 2
                    elif qs1 > 1:
                        if customer_name == full_name:
                            pass_flag = 2
                        else:
                            pass_flag = 1
                else:
                    qs2 = ChineseMedicineNormalize.select(standard_name=customer_name)
                    if qs2.count() > 0:
                        qs2 = orm.count(
                            item.standard_name
                            for item in ChineseMedicineNormalize
                            if customer_name in item.standard_name)
                        if qs2 == 1:
                            pass_flag = 2
                        elif qs2 > 1:
                            if customer_name == full_name:
                                pass_flag = 2
                            else:
                                pass_flag = 1
                    else:
                        qs3 = ChineseMedicineNormalize.select(other_name=customer_name)
                        if qs3.count() > 0:
                            qs3 = orm.count(
                                item.other_name
                                for item in ChineseMedicineNormalize
                                if customer_name in item.other_name)
                            if qs3 == 1:
                                pass_flag = 2
                            elif qs3 > 1:
                                if customer_name == full_name:
                                    pass_flag = 2
                                else:
                                    pass_flag = 1
                        else:
                            qs = Country.select(customer_name=customer_name)
                            if qs.count() > 0:
                                qs = orm.count(
                                    item.customer_name
                                    for item in Country
                                    if customer_name in item.customer_name)
                                if qs == 1:
                                    pass_flag = 2
                                elif qs > 1:
                                    if customer_name == full_name:
                                        pass_flag = 2
                                    else:
                                        pass_flag = 1
        elif remark == "医疗服务":
            qs1 = orm.count(
                service
                for service in ServiceNormalize
                if full_name in service.standard_name
            )
            if qs1 > 0:
                pass_flag = 2
                customer_name = full_name
            else:
                qs2 = orm.count(
                    service
                    for service in ServiceVersion
                    if full_name in service.version_name
                )
                if qs2 > 0:
                    pass_flag = 2
                    customer_name = full_name
                else:
                    qs1 = ServiceNormalize.select(standard_name=customer_name)
                    if qs1.count() > 0:
                        qs1 = orm.count(
                            item.standard_name
                            for item in ServiceNormalize
                            if customer_name in item.standard_name)
                        if qs1 == 1:
                            pass_flag = 2
                        elif qs1 > 1:
                            if customer_name == full_name:
                                pass_flag = 2
                            else:
                                pass_flag = 1
                    else:
                        qs2 = ServiceVersion.select(version_name=customer_name)
                        if qs2.count() > 0:
                            qs2 = orm.count(
                                item.standard_name
                                for item in ServiceNormalize
                                if customer_name in item.standard_name)
                            if qs2 == 1:
                                pass_flag = 2
                            elif qs2 > 1:
                                if customer_name == full_name:
                                    pass_flag = 2
                                else:
                                    pass_flag = 1
                            else:
                                pass_flag = 2
                        else:
                            qs = Country.select(customer_name=customer_name)
                            if qs.count() > 0:
                                qs = orm.count(
                                    item.customer_name
                                    for item in Country
                                    if customer_name in item.customer_name)
                                if qs == 1:
                                    pass_flag = 2
                                elif qs > 1:
                                    if customer_name == full_name:
                                        pass_flag = 2
                                    else:
                                        pass_flag = 1
        elif remark == "耗材":
            qs1 = orm.count(
                consumable
                for consumable in ConsumableNormalize
                if full_name in consumable.fourth_level_name
                or full_name in consumable.third_level_name
                or full_name in consumable.common_name
                or full_name in consumable.fourth_keyword_synonym
            )
            if qs1 > 0:
                pass_flag = 2
                customer_name = full_name
            else:
                qs1 = orm.select(
                    consumable
                    for consumable in ConsumableNormalize
                    if consumable.fourth_level_name == customer_name
                    or consumable.third_level_name == customer_name
                    or consumable.common_name == customer_name
                    or consumable.fourth_keyword_synonym == customer_name
                )
                if qs1.count() > 0:
                    pass_flag = 2
                else:
                    qs = Country.select(customer_name=customer_name)
                    if qs.count() > 0:
                        qs = orm.count(
                            item.customer_name
                            for item in Country
                            if customer_name in item.customer_name)
                        if qs == 1:
                            pass_flag = 2
                        elif qs > 1:
                            if customer_name == full_name:
                                pass_flag = 2
                            else:
                                pass_flag = 1

        return pass_flag, customer_name

    # 后处理
    def post_process(self, remark):
        print("开始后处理")
        path = self.input_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        # 输入校验
        self.input_check(df.columns.tolist(), path)
        if "charging_basis" in df.columns.tolist():
            df.drop("charging_basis", axis=1)
        # 数据规范去重
        df = df.applymap(lambda y: self.ch_en(y))
        df = df.drop_duplicates()

        table = []
        for index in tqdm(range(0, len(df), 200)):
            cache = []
            input_par = []
            for k in range(0, 200):
                if k + index >= len(df):
                    continue
                # 医院网站库格式
                item = df.iloc[index + k, :]
                series = pd.Series(item, index=self.columns)
                # 医院归一化人工处理
                # series["hospital_id"] = self.hospital_id
                # series["hospital"] = self.hospital
                # 网址人工预填
                # series["addr"] = self.addr
                series["remark"] = remark
                full_name = item["full_name"]
                if full_name:
                    # 后处理
                    input_par.append(
                        {
                            "医院名称": "",
                            "费用名称": full_name,
                            "number": "",
                            "amount": "",
                            "mapping_name": ""
                        })
                    cache.append(series)
                else:
                    table.append(series)

            res = post_process(input_par)
            for k in range(0, len(res)):
                pass_flag = 0
                customer_name = res[k]["mapping_name"]
                full_name = res[k]["name"]
                flag = res[k]["remark"]
                series = cache[k]
                if flag:
                    pass_flag = 2
                else:
                    pass_flag, customer_name = self.post_auto_pass(remark, customer_name, full_name)
                if customer_name:
                    series["customer_name"] = customer_name
                    series["pass_flag"] = pass_flag
                table.append(series)
            if index % 1000 == 0 or (index + 200) > len(df):
                df_output = pd.DataFrame(table)
                df_output = df_output.fillna("")
                df_output.to_excel(self.post_process_file_path, index=False)
                df_check = df_output[df_output["pass_flag"] != self.post_pass_flag][self.post_process_manual_columns]
                df_check.drop_duplicates(inplace=True)
                df_check.to_excel(self.post_process_manual_file_path, index=False)

    # 后处理人工审核后回填
    def update_manual_data_post(self):
        print("开始后处理人工审核值回填")
        manual_path = self.post_process_manual_file_path
        path = self.post_process_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        df1 = pd.read_excel(manual_path, dtype=str, keep_default_na=False)
        # 输入校验
        self.manual_input_check(df1.columns.tolist(), manual_path)
        for index, item in tqdm(df1.iterrows(), total=len(df1)):
            df.loc[:, "customer_name"][item["full_name"] == df["full_name"]] = item["customer_name"]
            df.loc[:, "pass_flag"][item["full_name"] == df["full_name"]] = self.post_pass_flag
        df.to_excel(self.post_process_update_file_path, index=False)

    # 添加大项
    @orm.db_session
    def add_type(self):
        print("开始添加大项")
        path = self.post_process_update_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        df_output = df.copy()
        for index, item in tqdm(df_output.iterrows(), total=len(df_output)):
            if item["remark"] == "耗材":
                item["type"] = "卫生材料费"
            if not item["type"]:
                customer_name = item["customer_name"]
                writed = 0
                if item["remark"] == "药品":
                    qs1 = orm.select(
                        medicine.nhsa_code
                        for medicine in MedicineNormalize
                        if medicine.standard_name == customer_name
                        or medicine.other_name == customer_name
                    )
                    if qs1.count() > 0:
                        for it in qs1:
                            if len(it) > 0:
                                if it[0] == "X":
                                    _type = "西药费"
                                    item["type"] = _type
                                    writed = 1
                                    break
                                elif it[0] == "Z":
                                    _type = "中成药费"
                                    item["type"] = _type
                                    writed = 1
                                    break
                    if not writed:
                        qs2 = orm.count(
                            CM
                            for CM in ChineseMedicineNormalize
                            if CM.standard_name == customer_name
                            or CM.other_name == customer_name
                        )
                        if qs2 > 0:
                            _type = "中药饮片"
                            item["type"] = _type
                            writed = 1
                if not writed:
                    qs = orm.select(
                        details.type
                        for details in Hospital
                        if details.customer_name == customer_name
                        and details.type
                    )
                    if qs.count() > 0:
                        for it in qs:
                            if len(it) > 2:
                                _type = it
                                item["type"] = _type
                                break

        df_output.to_excel(self.add_type_file_path, index=False)
        df_check = df_output[df_output["type"] == ""][self.type_manual_columns]
        df_check.drop_duplicates(inplace=True)
        df_check.to_excel(self.add_type_manual_file_path, index=False)

    # 生成入库文件
    def output(self):
        print("生成入库文件中...")
        path = self.normalize_update_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        if ((df["pass_flag"] != "2") & (df["pass_flag"] != 2)).any():
            print("后处理中存在未通过的条目，请检查pass_flag")
        if (df["type"] == "").any():
            print("大项存在空值，需要人工填充")
        shutil.copy(self.normalize_update_file_path, self.output_file_path)

    # 计算冗余项
    def add_parts(self):
        print("开始计算冗余项")
        path = self.post_process_update_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        table_parts = []
        for index, series in tqdm(df.iterrows(), total=len(df)):
            if series["full_name"]:
                series_parts = pd.Series(series, index=["full_name", "customer_name",
                                                        "sub_name1", "sub_name2", "sub_name3",
                                                        "specification",
                                                        "manufacturer",
                                                        "special_symbol", "others"])

                standard_name = series_parts["customer_name"]
                other_name = series_parts["full_name"]
                other_name = self.data_process.ch_en(other_name)
                if standard_name:
                    cust_name, parts = self.data_process.split_parts(other_name)
                    other_parts = self.data_process.deleted_words(other_name, standard_name)
                    if parts:
                        for k, v in parts.items():
                            if k != "其他":
                                for i in v.split(" "):
                                    if i and i in other_parts:
                                        other_parts = other_parts.replace(i, "")
                                        other_parts = other_parts.strip()

                        if not re.search("[0-9a-zA-Z\u4e00-\u9fa5]", other_parts):
                            other_parts = ""
                        if other_parts != parts["其他"]:
                            parts["其他"] += self.data_process.deleted_words(other_parts, parts["其他"])
                        for i, part in enumerate(parts["医用术语"].split(" ")):
                            if i < 3:
                                series_parts[f"sub_name{i + 1}"] = part
                        if not series_parts["specification"]:
                            series_parts["specification"] += parts["规格"]
                        if not series_parts["manufacturer"]:
                            series_parts["manufacturer"] = parts["药厂"]
                        series_parts["special_symbol"] = parts["特殊符号"]
                        series_parts["others"] = parts["其他"]
                table_parts.append(series_parts)
        df_output = pd.DataFrame(table_parts, dtype=str)
        df_output = df_output.fillna("")
        df_output.to_excel(self.add_parts_file_path, index=False)

    # 归一化人工审核后回填
    def update_manual_data_normal(self):
        print("开始归一化人工审核值回填")
        manual_path = self.normalize_manual_file_path
        path = self.normalize_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        df1 = pd.read_excel(manual_path, dtype=str, keep_default_na=False)
        # 输入校验
        self.manual_input_check(df1.columns.tolist(), manual_path)
        for index, item in tqdm(df1.iterrows(), total=len(df1)):
            df.loc[:, "standard_name"][item["version_name"] == df["version_name"]] = item["standard_name"]
            df.loc[:, "pass_flag2"][item["version_name"] == df["version_name"]] = 1
        df.to_excel(self.normalize_update_file_path, index=False)

    # 单元格数据处理
    def cell_process(self, path):
        print("读取中...")
        excel_file = pd.ExcelFile(path)
        sheet_names = excel_file.sheet_names
        if len(sheet_names) > 1:
            text = "请输入需要处理的sheet页序号：\n"
            for i, sheet_name in enumerate(sheet_names):
                text += f"{i}、{sheet_name}\n"
            for index in str(input(text)):
                sheet_name = sheet_names[int(index)]
                df = pd.read_excel(path, sheet_name=sheet_name, dtype=str, keep_default_na=False)
                self.cell_process_choose_col(df)

        else:
            df = pd.read_excel(path, dtype=str, keep_default_na=False)
            self.cell_process_choose_col(df)

    # 单元格数据处理，选择处理列
    def cell_process_choose_col(self, df):
        columns = df.columns.tolist()
        text = "请输入需要处理的列的名称：\n"
        for i, col in enumerate(columns):
            text += f"{i}、{col}\n"
        for index in str(input(text)):
            col = int(index)
            df.iloc[:, col] = df.apply(lambda x: self.cell_process_main(x, col), axis=1)
    # 单元格数据处理，输入单行数据，返回正确的名称
    def cell_process_main(self, item, col):
        word = item[col]
        word_list = re.split(r"\s", word)
        # for word in word_list:
        #     if len(word) < 3;




if __name__ == '__main__':
    dirs = []
    dir_path = "/kv-data/sw/查药扣费/数据/2-上海市"
    province_version_data_process = ProvinceVersionDataProcess(dir_path)
    province_version_data_process.main()
